package com.hr.dept.dao.Impl;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import com.hr.dept.dao.DeptUserRelationshipDao;
import com.hr.dept.domain.DeptUser;
import com.hr.user.domain.User;
import com.hr.utils.PageBean;
import com.hr.utils.TxQueryRunner;
/**
 * 
 * @author 杨谦
 * @date 2015-11-3
 *
 */
public class DeptUserRelationshipDaoImpl implements DeptUserRelationshipDao{
	private QueryRunner qr = new TxQueryRunner();
	
	@Override
	public List<User> findBydeptID(String dept_id) {
		// TODO Auto-generated method stub
		try {
			String sql = "select * from HR_USER where USER_ID IN (select USER_ID from DEPT_USER where dept_id = ? and state = '1')";
			return qr.query(sql, new BeanListHandler<User>(User.class),dept_id);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
	}
	
	@Override
	public PageBean<User> queryAll_Page(int PageCurrent,int PageSize,String dept_id) {
		// TODO Auto-generated method stub
		try {
			PageBean<User> pb = new PageBean<User>();
			//设置当前页面页码
			pb.setPageCurrent(PageCurrent);
			//设置当前页面的页面容量
			pb.setPageSize(PageSize);
			String sql = "select Count(*) from HR_USER where USER_ID IN (select USER_ID from DEPT_USER where dept_id=? and state = '1')";
			Number num = (Number)qr.query(sql,new ScalarHandler(),dept_id);
			int rowCount = num.intValue();//获取数据库中的所有记录数
			pb.setRowCount(rowCount);
			sql = "select * from (select a1.*,rownum rn from (select * from HR_USER where USER_ID IN (select USER_ID from DEPT_USER where dept_id=? and state = '1'))a1 where rownum<="+PageCurrent*PageSize+")where rn >="+((PageCurrent-1)*PageSize+1);
			List<User> list_user = qr.query(sql,new BeanListHandler<User>(User.class),dept_id);
			pb.setBeanList(list_user);
			return pb;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
	}
	
	@Override
	public void updateDeptUser(String user_id, String dept_id_before,
			String dept_id_after,String describle) {
		// TODO Auto-generated method stub
		//update DEPT_USER set CHANGEDATE = to_date('2010-2-12 10:20:30','YYYY-MM-DD HH24:MM:SS');
		Date now = new Date(); 
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//可以方便地修改日期格式
		String nowTime = dateFormat.format(now); 
		try {
			String sql1 = "update DEPT_USER set state = 0,CHANGEDATE=to_date('"+nowTime+"','YYYY-MM-DD HH24:MI:SS') where user_id = ? and dept_id = ?";
			Object[] params = {user_id,dept_id_before};
			qr.update(sql1, params);
			String sql2 = "insert into DEPT_USER values('"+dept_id_after+"','"+user_id+"','"+describle+"','"+"1"+"',to_date('"+nowTime+"','YYYY-MM-DD HH24:MI:SS'))";
			qr.update(sql2);
			String sql3 = "update HR_USER set dept_id = ? where user_id = ?";
			qr.update(sql3, dept_id_after,user_id);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	@Test
	public void test() {
		Date now = new Date(); 
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//可以方便地修改日期格式

		String hehe = dateFormat.format( now ); 
		System.out.println(hehe); 
	}
	
	/**
	 * 查询所有员工的调任记录
	 * @param PageCurrent
	 * @param PageSize
	 * @return
	 */
	@Override
	public PageBean<DeptUser> queryAll_change_record(int PageCurrent,
			int PageSize) {
		try {
			PageBean<DeptUser> pb = new PageBean<DeptUser>();
			//设置当前页面页码
			pb.setPageCurrent(PageCurrent);
			//设置当前页面的页面容量
			pb.setPageSize(PageSize);
			String sql = "select Count(*) from dept_user";
			Number num = (Number)qr.query(sql,new ScalarHandler());
			int rowCount = num.intValue();//获取数据库中的所有记录数
			pb.setRowCount(rowCount);
			sql = "select * from (select a1.*,rownum rn from (select c.user_id,a.username,c.dept_id,b.dept_name,c.changedate,c.state,c.describle from hr_user a,dept_table b,dept_user c where a.user_id IN (select user_id from dept_user where a.user_id=c.user_id) and b.dept_id IN (select dept_id from dept_user where b.dept_id=c.dept_id) order by c.changedate desc)a1 where rownum<="+PageCurrent*PageSize+" ) where rn >="+((PageCurrent-1)*PageSize+1);
			List<DeptUser> list_DeptUser = qr.query(sql,new BeanListHandler<DeptUser>(DeptUser.class));
			pb.setBeanList(list_DeptUser);
			return pb;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
	}
	
	@Test
	public void test_change_query(){
		PageBean<DeptUser> pageBean = queryAll_change_record(1,4);
		System.out.println(pageBean.getBeanList());
	}

	/**
	 * 拥有了查询所有员工权利后，再对于部门进行检测，查询自己部门的情况
	 * @param PageCurrent
	 * @param PageSize
	 * @param dept_id
	 * @return
	 */
	@Override
	public PageBean<DeptUser> queryAll_change_record_ByDeptID(int PageCurrent, int PageSize, String dept_id) {
		// TODO Auto-generated method stub
		try {
			PageBean<DeptUser> pb = new PageBean<DeptUser>();
			//设置当前页面页码
			pb.setPageCurrent(PageCurrent);
			//设置当前页面的页面容量
			pb.setPageSize(PageSize);
			String sql = "select Count(*) from hr_user a,dept_table b,dept_user c where a.user_id IN (select user_id from dept_user where a.user_id=c.user_id) and b.dept_id IN (select dept_id from dept_user where b.dept_id=c.dept_id) and a.dept_id=?";
			Number num = (Number)qr.query(sql,new ScalarHandler(),dept_id);
			int rowCount = num.intValue();//获取数据库中的所有记录数
			pb.setRowCount(rowCount);
			sql = "select * from (select a1.*,rownum rn from (select c.user_id,a.username,c.dept_id,b.dept_name,c.changedate,c.state,c.describle from hr_user a,dept_table b,dept_user c where a.user_id IN (select user_id from dept_user where a.user_id=c.user_id) and b.dept_id IN (select dept_id from dept_user where b.dept_id=c.dept_id) and a.dept_id=? order by c.changedate desc)a1 where rownum<="+PageCurrent*PageSize+" ) where rn >="+((PageCurrent-1)*PageSize+1);
			List<DeptUser> list_DeptUser = qr.query(sql,new BeanListHandler<DeptUser>(DeptUser.class),dept_id);
			pb.setBeanList(list_DeptUser);
			return pb;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
	}
	
	/**
	 * 查询当前登陆员工的自身的调任记录
	 * @param PageCurrent
	 * @param PageSize
	 * @param user_id
	 * @return
	 */
	@Override
	public PageBean<DeptUser> queryAll_change_record_ByUserID(int PageCurrent, int PageSize, String user_id) {
		// TODO Auto-generated method stub
		try {
			PageBean<DeptUser> pb = new PageBean<DeptUser>();
			//设置当前页面页码
			pb.setPageCurrent(PageCurrent);
			//设置当前页面的页面容量
			pb.setPageSize(PageSize);
			String sql = "select Count(*) from hr_user a,dept_table b,dept_user c where a.user_id IN (select user_id from dept_user where a.user_id=c.user_id) and b.dept_id IN (select dept_id from dept_user where b.dept_id=c.dept_id) and a.user_id=?";
			Number num = (Number)qr.query(sql,new ScalarHandler(),user_id);
			int rowCount = num.intValue();//获取数据库中的所有记录数
			pb.setRowCount(rowCount);
			sql = "select * from (select a1.*,rownum rn from (select c.user_id,a.username,c.dept_id,b.dept_name,c.changedate,c.state,c.describle from hr_user a,dept_table b,dept_user c where a.user_id IN (select user_id from dept_user where a.user_id=c.user_id) and b.dept_id IN (select dept_id from dept_user where b.dept_id=c.dept_id) and a.user_id=? order by c.changedate desc)a1 where rownum<="+PageCurrent*PageSize+" ) where rn >="+((PageCurrent-1)*PageSize+1);
			List<DeptUser> list_DeptUser = qr.query(sql,new BeanListHandler<DeptUser>(DeptUser.class),user_id);
			pb.setBeanList(list_DeptUser);
			return pb;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
	}
	
}
